{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "nums = os.listdir('./china_isd_lite_2021')\n",
    "nums = [i[0:6] for i in nums]\n",
    "# nums"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('./isd-history.csv',)\n",
    "# df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>USAF</th>\n",
       "      <th>WBAN</th>\n",
       "      <th>STATION NAME</th>\n",
       "      <th>CTRY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>ICAO</th>\n",
       "      <th>LAT</th>\n",
       "      <th>LON</th>\n",
       "      <th>ELEV(M)</th>\n",
       "      <th>BEGIN</th>\n",
       "      <th>END</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10294</th>\n",
       "      <td>450070</td>\n",
       "      <td>99999</td>\n",
       "      <td>HONG KONG INTL</td>\n",
       "      <td>HK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VHHH</td>\n",
       "      <td>22.309</td>\n",
       "      <td>113.915</td>\n",
       "      <td>8.5</td>\n",
       "      <td>19481231</td>\n",
       "      <td>20210419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10298</th>\n",
       "      <td>450110</td>\n",
       "      <td>99999</td>\n",
       "      <td>MACAU INTL</td>\n",
       "      <td>MC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VMMC</td>\n",
       "      <td>22.150</td>\n",
       "      <td>113.592</td>\n",
       "      <td>6.1</td>\n",
       "      <td>19510320</td>\n",
       "      <td>20210419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10300</th>\n",
       "      <td>450320</td>\n",
       "      <td>99999</td>\n",
       "      <td>TA KWU LING</td>\n",
       "      <td>CH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.533</td>\n",
       "      <td>114.150</td>\n",
       "      <td>13.0</td>\n",
       "      <td>19921204</td>\n",
       "      <td>20210419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10303</th>\n",
       "      <td>450350</td>\n",
       "      <td>99999</td>\n",
       "      <td>LAU FAU SHAN</td>\n",
       "      <td>CH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.467</td>\n",
       "      <td>113.983</td>\n",
       "      <td>35.0</td>\n",
       "      <td>20040713</td>\n",
       "      <td>20210419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10304</th>\n",
       "      <td>450390</td>\n",
       "      <td>99999</td>\n",
       "      <td>SHA TIN</td>\n",
       "      <td>CH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.400</td>\n",
       "      <td>114.200</td>\n",
       "      <td>8.0</td>\n",
       "      <td>20040713</td>\n",
       "      <td>20210419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12700</th>\n",
       "      <td>598550</td>\n",
       "      <td>99999</td>\n",
       "      <td>QIONGHAI</td>\n",
       "      <td>CH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.233</td>\n",
       "      <td>110.467</td>\n",
       "      <td>25.0</td>\n",
       "      <td>19560820</td>\n",
       "      <td>20210419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12704</th>\n",
       "      <td>599480</td>\n",
       "      <td>99999</td>\n",
       "      <td>SANYA</td>\n",
       "      <td>CH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.217</td>\n",
       "      <td>109.583</td>\n",
       "      <td>7.0</td>\n",
       "      <td>19560820</td>\n",
       "      <td>20210419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12708</th>\n",
       "      <td>599810</td>\n",
       "      <td>99999</td>\n",
       "      <td>XISHA DAO</td>\n",
       "      <td>CH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.833</td>\n",
       "      <td>112.333</td>\n",
       "      <td>5.0</td>\n",
       "      <td>19570701</td>\n",
       "      <td>20210419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12709</th>\n",
       "      <td>599850</td>\n",
       "      <td>99999</td>\n",
       "      <td>SANHU DAO</td>\n",
       "      <td>CH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.533</td>\n",
       "      <td>111.617</td>\n",
       "      <td>5.0</td>\n",
       "      <td>19750426</td>\n",
       "      <td>20210419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12712</th>\n",
       "      <td>599970</td>\n",
       "      <td>99999</td>\n",
       "      <td>NANSHA DAO</td>\n",
       "      <td>CH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.383</td>\n",
       "      <td>114.367</td>\n",
       "      <td>5.0</td>\n",
       "      <td>19730101</td>\n",
       "      <td>20210419</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>411 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         USAF   WBAN    STATION NAME CTRY STATE  ICAO     LAT      LON  \\\n",
       "10294  450070  99999  HONG KONG INTL   HK   NaN  VHHH  22.309  113.915   \n",
       "10298  450110  99999      MACAU INTL   MC   NaN  VMMC  22.150  113.592   \n",
       "10300  450320  99999     TA KWU LING   CH   NaN   NaN  22.533  114.150   \n",
       "10303  450350  99999    LAU FAU SHAN   CH   NaN   NaN  22.467  113.983   \n",
       "10304  450390  99999         SHA TIN   CH   NaN   NaN  22.400  114.200   \n",
       "...       ...    ...             ...  ...   ...   ...     ...      ...   \n",
       "12700  598550  99999        QIONGHAI   CH   NaN   NaN  19.233  110.467   \n",
       "12704  599480  99999           SANYA   CH   NaN   NaN  18.217  109.583   \n",
       "12708  599810  99999       XISHA DAO   CH   NaN   NaN  16.833  112.333   \n",
       "12709  599850  99999       SANHU DAO   CH   NaN   NaN  16.533  111.617   \n",
       "12712  599970  99999      NANSHA DAO   CH   NaN   NaN  10.383  114.367   \n",
       "\n",
       "       ELEV(M)     BEGIN       END  \n",
       "10294      8.5  19481231  20210419  \n",
       "10298      6.1  19510320  20210419  \n",
       "10300     13.0  19921204  20210419  \n",
       "10303     35.0  20040713  20210419  \n",
       "10304      8.0  20040713  20210419  \n",
       "...        ...       ...       ...  \n",
       "12700     25.0  19560820  20210419  \n",
       "12704      7.0  19560820  20210419  \n",
       "12708      5.0  19570701  20210419  \n",
       "12709      5.0  19750426  20210419  \n",
       "12712      5.0  19730101  20210419  \n",
       "\n",
       "[411 rows x 11 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['USAF'].isin(nums)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.loc[df['USAF'].isin(nums)].loc[(df['CTRY'] == \"CH\")]\n",
    "# df.loc[df['USAF'].isin(nums)]\n",
    "# city = ['BEIJING - CAPITAL INTERNATIONAL AIRPORT','SHANGHAI','TIANJIN','FUZHOU']\n",
    "# city_id = ['574940','592870','594930','575160']\n",
    "# df.loc[df['STATION NAME'].isin(city)]\n",
    "city_id = ['545110','545270','574940','575160','583620','588470','592870','594930']\n",
    "city_name = ['北京','天津','武汉','重庆','上海','福州','广州','深圳']\n",
    "STATION_NAME=df.loc[df['USAF'].isin(city_id)]['STATION NAME'].to_list()\n",
    "# STATION_NAME"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "def hebing(id,city_name,city):\n",
    "    df = pd.read_csv(f'./china_isd_lite_2021/{id}-99999-2021',names= ['年'])\n",
    "    datas=[i.split() for i in df.年.to_list()]\n",
    "    df1 = pd.DataFrame(datas,columns = ['年','月','日','时','空气温度','露点温度','海平面压力','风向','风速速率','天空状况总覆盖代码','在一个小时的积累周期内测量的液体沉淀深度','在6个小时的积累周期内测量的液体沉淀深度'])\n",
    "    df1['city_id'] = id\n",
    "    df1['city'] = city_name\n",
    "    df1['STATION_NAME'] = city\n",
    "    return df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         年   月   日   时  空气温度  露点温度  海平面压力     风向 风速速率 天空状况总覆盖代码  \\\n",
      "0     2021  01  01  00  -104  -175  10260     60   10     -9999   \n",
      "1     2021  01  01  01   -90  -170  -9999  -9999   10     -9999   \n",
      "2     2021  01  01  02   -50  -170  -9999  -9999   10     -9999   \n",
      "3     2021  01  01  03   -29  -189  10266     30   10     -9999   \n",
      "4     2021  01  01  04   -20  -180  -9999  -9999   10     -9999   \n",
      "...    ...  ..  ..  ..   ...   ...    ...    ...  ...       ...   \n",
      "6330  2021  09  26  17   280   220  -9999    100   30     -9999   \n",
      "6331  2021  09  26  18   271   229  10111     90   10     -9999   \n",
      "6332  2021  09  26  19   270   230  -9999    120   30     -9999   \n",
      "6333  2021  09  26  20   270   230  -9999     80   10     -9999   \n",
      "6334  2021  09  26  21   270   233  10111      2    6     -9999   \n",
      "\n",
      "     在一个小时的积累周期内测量的液体沉淀深度 在6个小时的积累周期内测量的液体沉淀深度 city_id city  \\\n",
      "0                   -9999                    0  545110   北京   \n",
      "1                   -9999                -9999  545110   北京   \n",
      "2                   -9999                -9999  545110   北京   \n",
      "3                   -9999                    0  545110   北京   \n",
      "4                   -9999                -9999  545110   北京   \n",
      "...                   ...                  ...     ...  ...   \n",
      "6330                -9999                -9999  594930   深圳   \n",
      "6331                -9999                -9999  594930   深圳   \n",
      "6332                -9999                -9999  594930   深圳   \n",
      "6333                -9999                -9999  594930   深圳   \n",
      "6334                -9999                -9999  594930   深圳   \n",
      "\n",
      "                                 STATION_NAME  \n",
      "0     BEIJING - CAPITAL INTERNATIONAL AIRPORT  \n",
      "1     BEIJING - CAPITAL INTERNATIONAL AIRPORT  \n",
      "2     BEIJING - CAPITAL INTERNATIONAL AIRPORT  \n",
      "3     BEIJING - CAPITAL INTERNATIONAL AIRPORT  \n",
      "4     BEIJING - CAPITAL INTERNATIONAL AIRPORT  \n",
      "...                                       ...  \n",
      "6330                               BAOAN INTL  \n",
      "6331                               BAOAN INTL  \n",
      "6332                               BAOAN INTL  \n",
      "6333                               BAOAN INTL  \n",
      "6334                               BAOAN INTL  \n",
      "\n",
      "[41977 rows x 15 columns]\n"
     ]
    }
   ],
   "source": [
    "df_datas = pd.DataFrame()\n",
    "for i in range(len(city_id)):\n",
    "    df_datas = pd.concat([df_datas,hebing(city_id[i],city_name[i],STATION_NAME[i])])\n",
    "print(df_datas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>空气温度</th>\n",
       "      <th>露点温度</th>\n",
       "      <th>海平面压力</th>\n",
       "      <th>风向</th>\n",
       "      <th>风速速率</th>\n",
       "      <th>天空状况总覆盖代码</th>\n",
       "      <th>在一个小时的积累周期内测量的液体沉淀深度</th>\n",
       "      <th>在6个小时的积累周期内测量的液体沉淀深度</th>\n",
       "      <th>city_id</th>\n",
       "      <th>city</th>\n",
       "      <th>STATION_NAME</th>\n",
       "      <th>时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-104</td>\n",
       "      <td>-175</td>\n",
       "      <td>10260</td>\n",
       "      <td>60</td>\n",
       "      <td>10</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "      <td>545110</td>\n",
       "      <td>北京</td>\n",
       "      <td>BEIJING - CAPITAL INTERNATIONAL AIRPORT</td>\n",
       "      <td>2021/01/01/00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-90</td>\n",
       "      <td>-170</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>10</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>545110</td>\n",
       "      <td>北京</td>\n",
       "      <td>BEIJING - CAPITAL INTERNATIONAL AIRPORT</td>\n",
       "      <td>2021/01/01/01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-50</td>\n",
       "      <td>-170</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>10</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>545110</td>\n",
       "      <td>北京</td>\n",
       "      <td>BEIJING - CAPITAL INTERNATIONAL AIRPORT</td>\n",
       "      <td>2021/01/01/02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-29</td>\n",
       "      <td>-189</td>\n",
       "      <td>10266</td>\n",
       "      <td>30</td>\n",
       "      <td>10</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "      <td>545110</td>\n",
       "      <td>北京</td>\n",
       "      <td>BEIJING - CAPITAL INTERNATIONAL AIRPORT</td>\n",
       "      <td>2021/01/01/03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-20</td>\n",
       "      <td>-180</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>10</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>545110</td>\n",
       "      <td>北京</td>\n",
       "      <td>BEIJING - CAPITAL INTERNATIONAL AIRPORT</td>\n",
       "      <td>2021/01/01/04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6330</th>\n",
       "      <td>280</td>\n",
       "      <td>220</td>\n",
       "      <td>-9999</td>\n",
       "      <td>100</td>\n",
       "      <td>30</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>594930</td>\n",
       "      <td>深圳</td>\n",
       "      <td>BAOAN INTL</td>\n",
       "      <td>2021/09/26/17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6331</th>\n",
       "      <td>271</td>\n",
       "      <td>229</td>\n",
       "      <td>10111</td>\n",
       "      <td>90</td>\n",
       "      <td>10</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>594930</td>\n",
       "      <td>深圳</td>\n",
       "      <td>BAOAN INTL</td>\n",
       "      <td>2021/09/26/18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6332</th>\n",
       "      <td>270</td>\n",
       "      <td>230</td>\n",
       "      <td>-9999</td>\n",
       "      <td>120</td>\n",
       "      <td>30</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>594930</td>\n",
       "      <td>深圳</td>\n",
       "      <td>BAOAN INTL</td>\n",
       "      <td>2021/09/26/19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6333</th>\n",
       "      <td>270</td>\n",
       "      <td>230</td>\n",
       "      <td>-9999</td>\n",
       "      <td>80</td>\n",
       "      <td>10</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>594930</td>\n",
       "      <td>深圳</td>\n",
       "      <td>BAOAN INTL</td>\n",
       "      <td>2021/09/26/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6334</th>\n",
       "      <td>270</td>\n",
       "      <td>233</td>\n",
       "      <td>10111</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>594930</td>\n",
       "      <td>深圳</td>\n",
       "      <td>BAOAN INTL</td>\n",
       "      <td>2021/09/26/21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>41977 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      空气温度  露点温度  海平面压力     风向 风速速率 天空状况总覆盖代码 在一个小时的积累周期内测量的液体沉淀深度  \\\n",
       "0     -104  -175  10260     60   10     -9999                -9999   \n",
       "1      -90  -170  -9999  -9999   10     -9999                -9999   \n",
       "2      -50  -170  -9999  -9999   10     -9999                -9999   \n",
       "3      -29  -189  10266     30   10     -9999                -9999   \n",
       "4      -20  -180  -9999  -9999   10     -9999                -9999   \n",
       "...    ...   ...    ...    ...  ...       ...                  ...   \n",
       "6330   280   220  -9999    100   30     -9999                -9999   \n",
       "6331   271   229  10111     90   10     -9999                -9999   \n",
       "6332   270   230  -9999    120   30     -9999                -9999   \n",
       "6333   270   230  -9999     80   10     -9999                -9999   \n",
       "6334   270   233  10111      2    6     -9999                -9999   \n",
       "\n",
       "     在6个小时的积累周期内测量的液体沉淀深度 city_id city  \\\n",
       "0                       0  545110   北京   \n",
       "1                   -9999  545110   北京   \n",
       "2                   -9999  545110   北京   \n",
       "3                       0  545110   北京   \n",
       "4                   -9999  545110   北京   \n",
       "...                   ...     ...  ...   \n",
       "6330                -9999  594930   深圳   \n",
       "6331                -9999  594930   深圳   \n",
       "6332                -9999  594930   深圳   \n",
       "6333                -9999  594930   深圳   \n",
       "6334                -9999  594930   深圳   \n",
       "\n",
       "                                 STATION_NAME             时间  \n",
       "0     BEIJING - CAPITAL INTERNATIONAL AIRPORT  2021/01/01/00  \n",
       "1     BEIJING - CAPITAL INTERNATIONAL AIRPORT  2021/01/01/01  \n",
       "2     BEIJING - CAPITAL INTERNATIONAL AIRPORT  2021/01/01/02  \n",
       "3     BEIJING - CAPITAL INTERNATIONAL AIRPORT  2021/01/01/03  \n",
       "4     BEIJING - CAPITAL INTERNATIONAL AIRPORT  2021/01/01/04  \n",
       "...                                       ...            ...  \n",
       "6330                               BAOAN INTL  2021/09/26/17  \n",
       "6331                               BAOAN INTL  2021/09/26/18  \n",
       "6332                               BAOAN INTL  2021/09/26/19  \n",
       "6333                               BAOAN INTL  2021/09/26/20  \n",
       "6334                               BAOAN INTL  2021/09/26/21  \n",
       "\n",
       "[41977 rows x 12 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_datas['时间'] = df_datas['年']+'/'+df_datas['月']+'/'+df_datas['日']+'/'+df_datas['时']\n",
    "df_datas=df_datas.drop(labels=['年',\"月\",\"日\",\"时\"],axis=1)\n",
    "df_datas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>时间</th>\n",
       "      <th>city_id</th>\n",
       "      <th>city</th>\n",
       "      <th>STATION_NAME</th>\n",
       "      <th>空气温度</th>\n",
       "      <th>露点温度</th>\n",
       "      <th>海平面压力</th>\n",
       "      <th>风向</th>\n",
       "      <th>风速速率</th>\n",
       "      <th>天空状况总覆盖代码</th>\n",
       "      <th>在一个小时的积累周期内测量的液体沉淀深度</th>\n",
       "      <th>在6个小时的积累周期内测量的液体沉淀深度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021/01/01/00</td>\n",
       "      <td>545110</td>\n",
       "      <td>北京</td>\n",
       "      <td>BEIJING - CAPITAL INTERNATIONAL AIRPORT</td>\n",
       "      <td>-104</td>\n",
       "      <td>-175</td>\n",
       "      <td>10260</td>\n",
       "      <td>60</td>\n",
       "      <td>10</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021/01/01/01</td>\n",
       "      <td>545110</td>\n",
       "      <td>北京</td>\n",
       "      <td>BEIJING - CAPITAL INTERNATIONAL AIRPORT</td>\n",
       "      <td>-90</td>\n",
       "      <td>-170</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>10</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021/01/01/02</td>\n",
       "      <td>545110</td>\n",
       "      <td>北京</td>\n",
       "      <td>BEIJING - CAPITAL INTERNATIONAL AIRPORT</td>\n",
       "      <td>-50</td>\n",
       "      <td>-170</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>10</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021/01/01/03</td>\n",
       "      <td>545110</td>\n",
       "      <td>北京</td>\n",
       "      <td>BEIJING - CAPITAL INTERNATIONAL AIRPORT</td>\n",
       "      <td>-29</td>\n",
       "      <td>-189</td>\n",
       "      <td>10266</td>\n",
       "      <td>30</td>\n",
       "      <td>10</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021/01/01/04</td>\n",
       "      <td>545110</td>\n",
       "      <td>北京</td>\n",
       "      <td>BEIJING - CAPITAL INTERNATIONAL AIRPORT</td>\n",
       "      <td>-20</td>\n",
       "      <td>-180</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>10</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6330</th>\n",
       "      <td>2021/09/26/17</td>\n",
       "      <td>594930</td>\n",
       "      <td>深圳</td>\n",
       "      <td>BAOAN INTL</td>\n",
       "      <td>280</td>\n",
       "      <td>220</td>\n",
       "      <td>-9999</td>\n",
       "      <td>100</td>\n",
       "      <td>30</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6331</th>\n",
       "      <td>2021/09/26/18</td>\n",
       "      <td>594930</td>\n",
       "      <td>深圳</td>\n",
       "      <td>BAOAN INTL</td>\n",
       "      <td>271</td>\n",
       "      <td>229</td>\n",
       "      <td>10111</td>\n",
       "      <td>90</td>\n",
       "      <td>10</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6332</th>\n",
       "      <td>2021/09/26/19</td>\n",
       "      <td>594930</td>\n",
       "      <td>深圳</td>\n",
       "      <td>BAOAN INTL</td>\n",
       "      <td>270</td>\n",
       "      <td>230</td>\n",
       "      <td>-9999</td>\n",
       "      <td>120</td>\n",
       "      <td>30</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6333</th>\n",
       "      <td>2021/09/26/20</td>\n",
       "      <td>594930</td>\n",
       "      <td>深圳</td>\n",
       "      <td>BAOAN INTL</td>\n",
       "      <td>270</td>\n",
       "      <td>230</td>\n",
       "      <td>-9999</td>\n",
       "      <td>80</td>\n",
       "      <td>10</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6334</th>\n",
       "      <td>2021/09/26/21</td>\n",
       "      <td>594930</td>\n",
       "      <td>深圳</td>\n",
       "      <td>BAOAN INTL</td>\n",
       "      <td>270</td>\n",
       "      <td>233</td>\n",
       "      <td>10111</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>41977 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 时间 city_id city                             STATION_NAME  \\\n",
       "0     2021/01/01/00  545110   北京  BEIJING - CAPITAL INTERNATIONAL AIRPORT   \n",
       "1     2021/01/01/01  545110   北京  BEIJING - CAPITAL INTERNATIONAL AIRPORT   \n",
       "2     2021/01/01/02  545110   北京  BEIJING - CAPITAL INTERNATIONAL AIRPORT   \n",
       "3     2021/01/01/03  545110   北京  BEIJING - CAPITAL INTERNATIONAL AIRPORT   \n",
       "4     2021/01/01/04  545110   北京  BEIJING - CAPITAL INTERNATIONAL AIRPORT   \n",
       "...             ...     ...  ...                                      ...   \n",
       "6330  2021/09/26/17  594930   深圳                               BAOAN INTL   \n",
       "6331  2021/09/26/18  594930   深圳                               BAOAN INTL   \n",
       "6332  2021/09/26/19  594930   深圳                               BAOAN INTL   \n",
       "6333  2021/09/26/20  594930   深圳                               BAOAN INTL   \n",
       "6334  2021/09/26/21  594930   深圳                               BAOAN INTL   \n",
       "\n",
       "      空气温度  露点温度  海平面压力     风向 风速速率 天空状况总覆盖代码 在一个小时的积累周期内测量的液体沉淀深度  \\\n",
       "0     -104  -175  10260     60   10     -9999                -9999   \n",
       "1      -90  -170  -9999  -9999   10     -9999                -9999   \n",
       "2      -50  -170  -9999  -9999   10     -9999                -9999   \n",
       "3      -29  -189  10266     30   10     -9999                -9999   \n",
       "4      -20  -180  -9999  -9999   10     -9999                -9999   \n",
       "...    ...   ...    ...    ...  ...       ...                  ...   \n",
       "6330   280   220  -9999    100   30     -9999                -9999   \n",
       "6331   271   229  10111     90   10     -9999                -9999   \n",
       "6332   270   230  -9999    120   30     -9999                -9999   \n",
       "6333   270   230  -9999     80   10     -9999                -9999   \n",
       "6334   270   233  10111      2    6     -9999                -9999   \n",
       "\n",
       "     在6个小时的积累周期内测量的液体沉淀深度  \n",
       "0                       0  \n",
       "1                   -9999  \n",
       "2                   -9999  \n",
       "3                       0  \n",
       "4                   -9999  \n",
       "...                   ...  \n",
       "6330                -9999  \n",
       "6331                -9999  \n",
       "6332                -9999  \n",
       "6333                -9999  \n",
       "6334                -9999  \n",
       "\n",
       "[41977 rows x 12 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for i in reversed(['时间','city_id','city','STATION_NAME']):\n",
    "    mid = df_datas[i]\n",
    "    df_datas.pop(i)\n",
    "    df_datas.insert(0,i,mid)\n",
    "df_datas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df_datas.to_excel('2021年0101-0926中国气象部分城市历史数据.xlsx',index=None)"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "c3ac3f88986c03f5f4ea9b167d463bfb61b25cb597313235fabe727a0f7e0da2"
  },
  "kernelspec": {
   "display_name": "Python 3.9.5 64-bit ('base': conda)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
